"""
数据分析案例步骤1-文件读取
1.设计一个类，完成数据封装
2.设计一个抽象类，完成文件读取
3.设计一个类，完成文本文件读取
4.设计一个类，完成JSON文件读取
5.进行数据需求的逻辑计算(计算每一天的销售额)
6.将数据写入数据库
"""
from file_define import TextReader, JSONReader
from pymysql import connect, cursors

text_file_reader = TextReader('2011年1月销售数据.txt')
jan_records = text_file_reader.read()
json_file_reader = JSONReader('2011年2月销售数据JSON.txt')
feb_records = json_file_reader.read()
all_records = jan_records + feb_records
print(f"一共有{len(all_records)}条销售记录")
data_dict = {}
for record in all_records:
    if record.date in data_dict.keys():
        data_dict[record.date] += record.amount
    else:
        data_dict[record.date] = record.amount

conn = connect(
    host='localhost',
    user='root',
    password='111246',
    autocommit=True,
)
cursor = conn.cursor()
cursor.execute("create database if not exists py_sql")
conn.select_db("py_sql")
cursor.execute(
    'create table if not exists orders(order_date varchar(20), '
    'order_id varchar(50), '
    'money float, '
    'province varchar(20))',
)
for record in all_records:
    sql = f"insert into orders(order_date, order_id, money,province) " \
           f"values('{record.date}', '{record.order_id}', '{record.amount}', '{record.province}')"
    # print(sql)
    cursor.execute(sql)
cursor.close()
conn.close()
